package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.IClassroomDao;
import cn.edu.klmy.models.classroom.Classroom;

@Repository
public class ClassroomDao extends JdbcDaoSupport implements IClassroomDao {

    private static final String ADD_CLASSROOM = "INSERT INTO CLASSROOM(CLASSROOM_LOCATION) VALUES(?)";

    @Override
    public void addClassroom(Classroom classroom) {
        getJdbcTemplate().update(ADD_CLASSROOM, new Object[]{classroom.getClassroomLocation()});
    }

    private static final String UPDATE_CLASSROOM = "UPDATE CLASSROOM SET CLASSROOM_LOCATION = ? WHERE ID = ?";

    @Override
    public void updateClassroom(Classroom classroom) {
        getJdbcTemplate().update(UPDATE_CLASSROOM, new Object[]{classroom.getClassroomLocation(), classroom.getId()});
    }

    private static final String REMOVE_CLASSROOM = "DELETE FROM CLASSROOM WHERE ID = ?";

    @Override
    public void removeClassroom(int classroomId) {
        getJdbcTemplate().update(REMOVE_CLASSROOM, new Object[]{classroomId});
    }

    private static final String CLASSROOM_EXISTS = "SELECT COUNT(*) FROM CLASSROOM WHERE CLASSROOM_LOCATION = ?";

    @Override
    public boolean classroomExists(String classroomLocation) {
        int result = getJdbcTemplate().queryForInt(CLASSROOM_EXISTS, new Object[]{classroomLocation});

        if (result < 1) {
            return false;
        }
        return true;
    }

    private static final String GET_CLASSROOMS = "SELECT * FROM CLASSROOM WHERE ID = ?";

    @Override
    public Classroom getClassroom(int classroomId) {
        List<Classroom> classrooms = (List<Classroom>) getJdbcTemplate().query(GET_CLASSROOMS, new Object[]{classroomId}, new ClassroomMapper());
        if (null != classrooms && !classrooms.isEmpty())
            return classrooms.get(0);
        return null;
    }

    @Override
    public List<Classroom> getClassrooms(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("CLASSROOM", filter, pageNum, pageSize);
        return (List<Classroom>) getJdbcTemplate().query(sql, new Object[]{}, new ClassroomMapper());
    }

    @Override
    public int getClassroomCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("CLASSROOM", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    //add by gw
    public List<Classroom> getAllClassroom(){
        String sql="SELECT * FROM CLASSROOM";
        return getJdbcTemplate().query(sql,new ClassroomMapper());
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    private static class ClassroomMapper implements RowMapper<Classroom> {

        @Override
        public Classroom mapRow(ResultSet resultSet, int i) throws SQLException {
            Classroom classroom = new Classroom();
            classroom.setId(resultSet.getInt("ID"));
            classroom.setClassroomLocation(resultSet.getString("CLASSROOM_LOCATION"));
            return classroom;
        }
    }
}
